const { DataTypes } = require('sequelize');

/**
 * 创建每日一句和通知相关的数据库表
 */
module.exports = {
  async up(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();
    
    try {
      console.log('🏗️  开始创建每日一句和通知相关表...');

      // 1. 每日一句表
      await queryInterface.createTable('daily_sentences', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '每日一句ID'
        },
        date: {
          type: DataTypes.DATEONLY,
          allowNull: false,
          unique: true,
          comment: '日期'
        },
        chinese: {
          type: DataTypes.TEXT,
          allowNull: false,
          comment: '中文内容'
        },
        pinyin: {
          type: DataTypes.TEXT,
          allowNull: true,
          comment: '拼音标注'
        },
        english: {
          type: DataTypes.TEXT,
          allowNull: true,
          comment: '英文翻译'
        },
        source: {
          type: DataTypes.STRING(200),
          allowNull: true,
          comment: '来源'
        },
        audio_url: {
          type: DataTypes.STRING(500),
          allowNull: true,
          comment: '音频URL'
        },
        difficulty: {
          type: DataTypes.ENUM('easy', 'medium', 'hard'),
          defaultValue: 'medium',
          comment: '难度级别'
        },
        tags: {
          type: DataTypes.JSON,
          allowNull: true,
          comment: '标签'
        },
        view_count: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '查看次数'
        },
        practice_count: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '练习次数'
        },
        is_active: {
          type: DataTypes.BOOLEAN,
          defaultValue: true,
          comment: '是否启用'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '每日一句表'
      });

      // 2. 通知消息表
      await queryInterface.createTable('notifications', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '通知ID'
        },
        user_id: {
          type: DataTypes.INTEGER,
          allowNull: false,
          comment: '用户ID'
        },
        title: {
          type: DataTypes.STRING(200),
          allowNull: false,
          comment: '通知标题'
        },
        content: {
          type: DataTypes.TEXT,
          allowNull: false,
          comment: '通知内容'
        },
        type: {
          type: DataTypes.ENUM('system', 'course', 'activity', 'achievement'),
          defaultValue: 'system',
          comment: '通知类型'
        },
        status: {
          type: DataTypes.ENUM('read', 'unread'),
          defaultValue: 'unread',
          comment: '阅读状态'
        },
        extra: {
          type: DataTypes.JSON,
          allowNull: true,
          comment: '额外数据'
        },
        read_at: {
          type: DataTypes.DATE,
          allowNull: true,
          comment: '阅读时间'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '通知消息表'
      });

      // 3. 文件上传记录表
      await queryInterface.createTable('file_uploads', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '文件上传ID'
        },
        user_id: {
          type: DataTypes.INTEGER,
          allowNull: false,
          comment: '上传用户ID'
        },
        original_name: {
          type: DataTypes.STRING(255),
          allowNull: false,
          comment: '原始文件名'
        },
        filename: {
          type: DataTypes.STRING(255),
          allowNull: false,
          comment: '存储文件名'
        },
        file_path: {
          type: DataTypes.STRING(500),
          allowNull: false,
          comment: '文件路径'
        },
        file_url: {
          type: DataTypes.STRING(500),
          allowNull: false,
          comment: '文件访问URL'
        },
        file_type: {
          type: DataTypes.ENUM('audio', 'video', 'image', 'document'),
          allowNull: false,
          comment: '文件类型'
        },
        mime_type: {
          type: DataTypes.STRING(100),
          allowNull: false,
          comment: 'MIME类型'
        },
        file_size: {
          type: DataTypes.INTEGER,
          allowNull: false,
          comment: '文件大小(字节)'
        },
        duration: {
          type: DataTypes.FLOAT,
          allowNull: true,
          comment: '媒体文件时长(秒)'
        },
        thumbnail: {
          type: DataTypes.STRING(500),
          allowNull: true,
          comment: '缩略图URL'
        },
        usage_type: {
          type: DataTypes.ENUM('detection', 'game', 'daily', 'course', 'avatar', 'other'),
          allowNull: false,
          comment: '使用类型'
        },
        related_id: {
          type: DataTypes.INTEGER,
          allowNull: true,
          comment: '关联记录ID'
        },
        status: {
          type: DataTypes.ENUM('uploading', 'completed', 'failed', 'deleted'),
          defaultValue: 'uploading',
          comment: '上传状态'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '文件上传记录表'
      });

      // 4. 用户学习统计表
      await queryInterface.createTable('user_study_statistics', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '统计ID'
        },
        user_id: {
          type: DataTypes.INTEGER,
          allowNull: false,
          unique: true,
          comment: '用户ID'
        },
        total_study_time: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '总学习时长(秒)'
        },
        total_practices: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '总练习次数'
        },
        average_score: {
          type: DataTypes.DECIMAL(5, 2),
          defaultValue: 0,
          comment: '平均分数'
        },
        best_score: {
          type: DataTypes.DECIMAL(5, 2),
          defaultValue: 0,
          comment: '最高分数'
        },
        continuous_days: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '连续学习天数'
        },
        total_days: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '总学习天数'
        },
        last_study_date: {
          type: DataTypes.DATEONLY,
          allowNull: true,
          comment: '最后学习日期'
        },
        weekly_data: {
          type: DataTypes.JSON,
          allowNull: true,
          comment: '周统计数据'
        },
        monthly_data: {
          type: DataTypes.JSON,
          allowNull: true,
          comment: '月统计数据'
        },
        score_distribution: {
          type: DataTypes.JSON,
          allowNull: true,
          comment: '分数分布'
        },
        improvements: {
          type: DataTypes.JSON,
          allowNull: true,
          comment: '进步数据'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '用户学习统计表'
      });

      // 添加外键约束
      await queryInterface.addConstraint('notifications', {
        fields: ['user_id'],
        type: 'foreign key',
        name: 'fk_notifications_user_id',
        references: {
          table: 'users',
          field: 'id'
        },
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
        transaction
      });

      await queryInterface.addConstraint('file_uploads', {
        fields: ['user_id'],
        type: 'foreign key',
        name: 'fk_file_uploads_user_id',
        references: {
          table: 'users',
          field: 'id'
        },
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
        transaction
      });

      await queryInterface.addConstraint('user_study_statistics', {
        fields: ['user_id'],
        type: 'foreign key',
        name: 'fk_user_study_statistics_user_id',
        references: {
          table: 'users',
          field: 'id'
        },
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
        transaction
      });

      // 添加索引
      await queryInterface.addIndex('daily_sentences', ['date'], {
        name: 'idx_daily_sentences_date',
        transaction
      });

      await queryInterface.addIndex('notifications', ['user_id', 'status'], {
        name: 'idx_notifications_user_status',
        transaction
      });

      await queryInterface.addIndex('file_uploads', ['user_id', 'usage_type'], {
        name: 'idx_file_uploads_user_usage',
        transaction
      });

      console.log('✅ 每日一句和通知相关表创建完成');
      await transaction.commit();

    } catch (error) {
      await transaction.rollback();
      console.error('❌ 创建每日一句和通知表失败:', error);
      throw error;
    }
  },

  async down(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();
    
    try {
      console.log('🗑️  开始删除每日一句和通知相关表...');

      await queryInterface.dropTable('user_study_statistics', { transaction });
      await queryInterface.dropTable('file_uploads', { transaction });
      await queryInterface.dropTable('notifications', { transaction });
      await queryInterface.dropTable('daily_sentences', { transaction });

      console.log('✅ 每日一句和通知相关表删除完成');
      await transaction.commit();

    } catch (error) {
      await transaction.rollback();
      console.error('❌ 删除每日一句和通知表失败:', error);
      throw error;
    }
  }
};
